using System;
using System.Collections.Generic;
using System.Data;
using System.Windows.Forms;
using System.Threading;
using System.Data.SqlClient;
using System.IO;

/*
	Note to future self:
	this form orginally handled table copy only. Some time later
	the scripting and copying of other objects was added. 
	Hence some methods and variables are named for tables rather than
	database object names.

TODO:
	* - check box logic between script and copy content. Validate and give an explanation why not and alternatives.
	* - implement copy view / proc
		* - include object type in check for existence 
		* - copy tables generalised to copy objects.
	- Remove hard-coded "table" from labels/messages.
	- implement script for tables
	- implement script view / proc
	- snags:
		* - cater for schemas in if exists
		* - cater for don't keep the schema option.
	- find snags
		- add check existence of schema
		- target object checked not source
	- all combinations
	- preserve options.

*/
namespace SqlServerDriver
{
	public partial class CopyTableForm : Form
	{
		// members
		private CopyTablePresenter presenter; 
		
		// process handling - copy in progres flag
		private bool isCopying = false;
		private bool refreshData = false; // special case where table exists
		// cancel event -definition
		public delegate void CancelCopyHandler(object o, EventArgs e);
		// the event itself
		public static event CancelCopyHandler CancelIt;

		List<string> otherTables = new List<string>();
		bool isSynchMessageShown = false;

		// Constructor
		public CopyTableForm(CopyTablePresenter presenterIn, DbViewDriver.IConnectionConfig connectionConfig)
		{
			InitializeComponent();
			this.presenter = presenterIn;
			// register self as the view with the controller
			this.presenter.View = this;
		}

		///////////////////////////////////////////////////////////////////////////////////////////
		// Api for the View to call
		public DbView.ConnectionInfo SelectTargetDatabase(DbViewDriver.IConnectionConfig connectionConfig)
		{
			DbView.ConnectionInfo newCi = null; // null means no change required

			SqlServerConnectionEditForm openDlg = new SqlServerConnectionEditForm(connectionConfig);
			if (openDlg.ShowDialog(this) == DialogResult.OK)
			{
			    newCi = connectionConfig.GetCurrentConnection();
			}
			return newCi;
		}


		// Event handlers
		private void CopyTableForm_Load(object sender, EventArgs e)
		{
			// set up the caption
			this.Text = this.Text.Replace("$O$", this.presenter.Model.ObjectType.ToString());

			// bindings
			this.labelSourceDb.DataBindings.Add("Text", this.presenter.Model, "SourceDb", false, DataSourceUpdateMode.OnPropertyChanged);
			this.labelSourceTable.DataBindings.Add("Text", this.presenter.Model, "SourceTable", false, DataSourceUpdateMode.OnPropertyChanged);
			this.labelTargetDb.DataBindings.Add("Text", this.presenter.Model, "TargetDb", false, DataSourceUpdateMode.OnPropertyChanged);
			this.labelTargetTable.DataBindings.Add("Text", this.presenter.Model, "TargetTable", false, DataSourceUpdateMode.OnPropertyChanged);
			this.labelOtherTables.DataBindings.Add("Text", this.presenter.Model, "OtherTables", false, DataSourceUpdateMode.OnPropertyChanged);
			this.labelSrcObjectType.DataBindings.Add("Text", this.presenter.Model, "ObjectType", false, DataSourceUpdateMode.OnPropertyChanged);
			this.labelTgtObjectType.DataBindings.Add("Text", this.presenter.Model, "ObjectType", false, DataSourceUpdateMode.OnPropertyChanged);

			this.checkCopyContents.DataBindings.Add("Checked", this.presenter.Model, "IsDataWanted", false, DataSourceUpdateMode.OnPropertyChanged);
			this.checkuseCopyName.DataBindings.Add("Checked", this.presenter.Model, "IsCopyNameWanted", false, DataSourceUpdateMode.OnPropertyChanged);
			this.checkUseSourceSchema.DataBindings.Add("Checked", this.presenter.Model, "IsSchemaPreserved", false, DataSourceUpdateMode.OnPropertyChanged);
			this.checkScript.DataBindings.Add("Checked", this.presenter.Model, "IsScriptWanted", false, DataSourceUpdateMode.OnPropertyChanged);

			// Source table has no schema prefix
			if (this.presenter.Model.SourceTable.IndexOf(".") == -1) 
				// so disable the control (state of m_UseSourceSchema will be irrelevant
				this.checkUseSourceSchema.Enabled = false;

			// ask for the connection directly if source and target are the same (Initial entry)
			if (this.presenter.SourceMatchesDestination()) 
				this.BtnBrowse_Click(sender, e);

			// hide copy content unless copying tables
			this.checkCopyContents.Visible = this.presenter.Model.ObjectType == CopyTableModel.CopyObjectType.Table;
		}

		private void CopyTableForm_FormClosing(object sender, FormClosingEventArgs e)
		{
			// can't close while copying - press the cancel button instead
			if (this.isCopying)
				e.Cancel = true;
		}

		private void CheckuseCopyName_CheckedChanged(object sender, EventArgs e)
		{
			// in the event so we have to refresh the model manually
			CheckBox cb = sender as CheckBox;
			if (cb.Focused)
				cb.DataBindings[0].WriteValue();
			
			this.presenter.ModifyTargetObjectName();
			// and update the label unfortunately
			this.labelTargetTable.DataBindings[0].ReadValue();
		}

		private void CheckuseSourceSchema_CheckedChanged(object sender, EventArgs e)
		{
			CheckBox cb = sender as CheckBox;
			// refresh the model manually if control has focus
			if (cb.Focused)
				cb.DataBindings[0].WriteValue(); 
			
			this.presenter.ModifyTargetObjectName();
			// and update the label unfortunately
			this.labelTargetTable.DataBindings[0].ReadValue();
		}

		private void BtnOk_Click(object sender, EventArgs e)
		{
			OnExecute();
		}

		private void OnExecute()
		{
			// If trying to copy to the same place
			if (this.presenter.SourceMatchesDestination() && !this.presenter.Model.IsScriptWanted)  // 
			{
				MessageBox.Show("Source and destination are the same", "Copy not permitted",
								 MessageBoxButtons.OK, MessageBoxIcon.Warning);
			}
			else if (this.presenter.Model.IsDataWanted && this.presenter.Model.IsScriptWanted)
			{
				string noContentScripMessage = @"
While it might seem logical to check to script and copy content
expecting the table data to be scripted, it is not permitted at
present due to the potential for creating a giant script that
takes ages to generate. Your options are either to copy the table 
and contents directly or script the schema here and use the 
script data right click option available when the table data 
is displayed.";
				MessageBox.Show(noContentScripMessage, "Script Content not permitted here",
								 MessageBoxButtons.OK, MessageBoxIcon.Warning);
			}
			else
			{
				ResolveOtherTables();
				//
				if (this.presenter.Model.IsScriptWanted)
				{
					// get the file name and hold it in the model
					if (!this.SelectScriptFileName())
						return;

					this.presenter.InitialiseConnectionInfo();
					string fileName = this.presenter.Model.ScriptFileName;
					StringWriter writer = new StringWriter();
					// TODO: the Unicode conundrum
					System.Text.Encoding fileEncoding = (false) ? System.Text.Encoding.Unicode : System.Text.Encoding.Default;
					using (TextWriter fileWriter = new StreamWriter(fileName, false, fileEncoding))
					{
						List<string> allObjects = new List<string>() { this.presenter.Model.SourceTable };
						allObjects.AddRange(this.otherTables);
						foreach (string dbobject in allObjects)
						{
							try 
							{
								string oneScript = this.presenter.ScriptObject(dbobject);
								// add script to file
								writer.WriteLine(oneScript);
								// insert GO?
								writer.WriteLine("GO");
							}
							catch (Exception exc)
							{
								string message = @"
Could not script an item. The error message
is shown below. Do you wish to continue?
{0}";
								if (MessageBox.Show(string.Format(message, exc.Message), "Could not script "+dbobject, MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.No)
									return;
							}
						}
						fileWriter.Write(writer.ToString());
						writer.Close();
						fileWriter.Close();
					}
					MessageBox.Show(this, "File "+fileName+" has been created", "Scripting Complete",MessageBoxButtons.OK, MessageBoxIcon.Information);
					// 
					this.presenter.OnClose(true);
					this.Close();
				}
				else
					// do the first. 
					ExecuteOneObject();
			}
		}

		private bool SelectScriptFileName()
		{
			SaveFileDialog createFileDialog = new SaveFileDialog();

			createFileDialog.FileName = Path.GetFileName(this.presenter.Model.ScriptFileName);
			createFileDialog.Filter = "SQL Files (*.sql)|*.sql";
			createFileDialog.RestoreDirectory = true;

			if (createFileDialog.ShowDialog() == DialogResult.OK)
			{
				this.presenter.Model.ScriptFileName = createFileDialog.FileName;
				return true;
			}
			return false;
		}


		private void ExecuteOneObject()
		{
			switch (this.presenter.Model.ObjectType)
			{
				case CopyTableModel.CopyObjectType.Table:
					this.DoTableCopy();
					break;
				case CopyTableModel.CopyObjectType.View:
					this.DoObjectCopy(this.presenter.Model.ObjectType);
					break;
				case CopyTableModel.CopyObjectType.Procedure:
					this.DoObjectCopy(this.presenter.Model.ObjectType);
					break;
			}
		}

		private void ResolveOtherTables()
		{
			this.otherTables.Clear();
			this.otherTables.AddRange(this.presenter.UnMemoiseOtherTables());
		}

		private void DoTableCopy()
		{
			// do it
			DoTableCopy2();
			// copying data is done on a thread. Copying the schema is not.
			if (!this.presenter.Model.IsDataWanted)
				Progress(100, 100);
		}



		private void PrepareForNextObjectCopy(string objectName)
		{
			// fix up the model
			this.presenter.Model.SourceTable = objectName;
			this.presenter.Model.TargetTable = objectName;
			this.presenter.ModifyTargetObjectName();
		}

		private void BtnCancel_Click(object sender, EventArgs e)
		{
			// cancel while copying
			if (this.isCopying)
			{
			    if (CancelIt != null)
			    {
			        this.isCopying = false;
			        CancelIt(this, e);
			    }
			}
			// cancel the copy set-up
			else
			{
				this.DialogResult = DialogResult.Cancel;
				this.presenter.OnClose(false);
				this.Close();
			}
		}

		private void BtnBrowse_Click(object sender, EventArgs e)
		{
			this.presenter.UserRequestedSelectTargetDatabase();
			this.labelTargetDb.DataBindings[0].ReadValue();
		}
		
		private void btnSelect_Click(object sender, EventArgs e)
		{
			// 
			ObjectListModel model = new ObjectListModel();
			string [] selected = this.presenter.UnMemoiseOtherTables();
			foreach (string table in selected)
				model.ItemsSelected.Add(table);

			// get a list of table names 
			List<string> pick = new List<string>(this.presenter.GetAvailableObjectNames(this.presenter.Model.ObjectType));

			SelectObjectListController controller = new SelectObjectListController(model);
			controller.SetSelectionList(pick);
			SelectObjectListView dlg = new SelectObjectListView(controller, this.presenter.Model.ObjectType.ToString());
			if (dlg.ShowDialog(this) == DialogResult.OK)
			{
			    this.presenter.MemoiseOtherTables(model.ItemsSelected.ToArray());
				labelOtherTables.DataBindings[0].ReadValue();
			}
		}

		private void CopyStatus()
		{
			// all disabled except cancel
			this.btnOk.Enabled = !this.isCopying;
			this.btnBrowse.Enabled = !this.isCopying;
			this.checkCopyContents.Enabled = !this.isCopying;
			this.checkuseCopyName.Enabled = !this.isCopying;
			this.checkScript.Enabled = !this.isCopying;
		}

		// possibly not needed now we are threaded
		private void CopyProgress2(object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e)
		{
			long i = e.RowsCopied;
			labelProgress.Text = String.Format("{0}", i);
			this.Refresh();
			e.Abort = false;
		}

		// declare the callback function signature for the threaded copy 
		delegate void ShowProgressDelegate(int total, int done);

		// function to use to receive the prgress data
		private void Progress(int total, int done)
		{
			if (this.progressBar1.Maximum != total)
				this.progressBar1.Maximum = total;
			this.progressBar1.Value = done;
			labelProgress.Text = String.Format("{0}", done);
			labelCount.Text = String.Format("of {0}", total);
			this.Refresh();
			if (done == total)
			{
				this.isCopying = false;
				this.DialogResult = DialogResult.OK;
				// extras for refresh
				if (this.refreshData)
					KickoffRefresh();
				// here we hack in the extra table stuff
				CopyNextOtherTable();
			}
		}

		private void CopyNextOtherTable()
		{
			if (this.otherTables.Count == 0)
			{
				this.presenter.OnClose(true);
				this.Close();
			}
			else
			{
				// remove from queue
				string table = this.otherTables[0];
				this.otherTables.RemoveAt(0);
				// fix up model and any other state
				this.PrepareForNextObjectCopy(table);

				int totalOther = this.presenter.UnMemoiseOtherTables().Length;
				int currentTable = totalOther - this.otherTables.Count;
				string message = string.Format("{0}/{1}: {2}", currentTable +1, totalOther +1, table); // +1 to account for the primary.
				this.groupBoxProgress.Text = message;
				//this.Refresh();
				
				// progress for the scheme only case.
				if (!this.presenter.Model.IsDataWanted)
				{
				    this.progressBar1.Maximum = totalOther +1;
				    this.progressBar1.Value = currentTable +1;
					
				}

				// create and run the copy thread 
				// this.DoTableCopy();
				this.ExecuteOneObject();
			}
		}

		private void DoObjectCopy(CopyTableModel.CopyObjectType objectType)
		{
			// pass 1 non threaded
			// Get source and tgt connections and make checks
			this.presenter.InitialiseCopyRun();
			
			this.refreshData = false; // clear reset flag 
			
			if (this.presenter.IsCopyErr == true)
			{
				MessageBox.Show(this.presenter.LastError, "Cannot proceed", MessageBoxButtons.OK, MessageBoxIcon.Error);
				CopyNextOtherTable();
			    return; 
			}

			// If table exists on the target we need a prompt for it.
			bool objectExists = this.presenter.CheckObjectExistsOnTarget();
			if (objectExists)
			{
				if (ConfirmOkToOverwriteObject(this.presenter.Model.TargetTable))
				{
					// table copy includes logic here
				}
				else
				{
					CopyNextOtherTable();
					return; 
				}
			}

			this.presenter.CreateTargetObject(objectExists);
			if (this.presenter.IsCopyErr)
				MessageBox.Show(this.presenter.LastError, "Error creating the target object", MessageBoxButtons.OK, MessageBoxIcon.Error);

			// on to the next or finish
			CopyNextOtherTable();
		}

		private void DoTableCopy2()
		{
			// Get source and tgt connections and make checks
			this.presenter.InitialiseCopyRun();
			
			this.refreshData = false; // clear reset flag 
			
			if (this.presenter.IsCopyErr == true)
			{
				MessageBox.Show(this.presenter.LastError, "Cannot proceed", MessageBoxButtons.OK, MessageBoxIcon.Error);
				CopyNextOtherTable();
			    return; 
			}

			// If table exists on the target we need a prompt for it.
			if (this.presenter.CheckObjectExistsOnTarget())
			{
				if (ConfirmOkToOverwriteData(this.presenter.Model.TargetTable))
				{
					this.presenter.Model.IsCopyNameWanted = true;
					this.presenter.ModifyTargetObjectName();
					this.refreshData = true;
				}
				else
				{
					CopyNextOtherTable();
					return; 
				}
			}

			// copy schema
			this.presenter.CreateTargetTable();
			if (this.presenter.IsCopyErr)
			{
				MessageBox.Show(this.presenter.LastError, "Error creating the target table", MessageBoxButtons.OK, MessageBoxIcon.Error);
				CopyNextOtherTable();
				return;
			}

			if (this.presenter.Model.IsDataWanted)
			{
				// deal with the data
				CopyThreadClass wc = new CopyThreadClass(this, new ShowProgressDelegate(Progress),
															this.presenter.SourceConnectionString,
															this.presenter.TargetConnectionString,
															this.presenter.Model.SourceTable,
															this.presenter.Model.TargetTable);
				CancelIt += wc.CancelCopy;
				// do it
				Thread t = new Thread(new ThreadStart(wc.RunProcess));
				t.IsBackground = true; //make them a daemon - prevent thread callback issues
			    // set state to copy and update the UI
			    this.isCopying = true;
			    CopyStatus();
				// GO!
				t.Start();
			}
		}

		private void KickoffRefresh()
		{
			this.presenter.RunBackGroundTableUpdate();
			// Once is enough for this message
			if (!isSynchMessageShown)
				MessageBox.Show(this, String.Format(SYNC_MESSAGE, this.presenter.Model.TargetTable), "Synchronisation in Progress", MessageBoxButtons.OK, MessageBoxIcon.Information);
			isSynchMessageShown = true;
			// if refreshing then we have set the use copy name flag to on behind the scenes
			// it must be unset or it will "stick" for the next use
			// Note: we'll only be at this spot if the flag was originally off so this is safe
			this.presenter.Model.IsCopyNameWanted = false;
		}

		private bool ConfirmOkToOverwriteData(string targetTable)
		{
			String message = String.Format(CONFIRM_SYNC, targetTable);
			// note sets the default to No.
			DialogResult dr = MessageBox.Show(this, message, "Confirmation Required", MessageBoxButtons.YesNo,
			                                    MessageBoxIcon.Question, MessageBoxDefaultButton.Button2);
			if (dr == DialogResult.No) return false; // false halts the whole operation.
			return true;
		}

		private bool ConfirmOkToOverwriteObject(string targetObject)
		{
			String message = String.Format(CONFIRM_OVERWRITE, targetObject);
			// note sets the default to No.
			DialogResult dr = MessageBox.Show(this, message, "Confirmation Required", MessageBoxButtons.YesNo,
			                                    MessageBoxIcon.Question, MessageBoxDefaultButton.Button2);
			if (dr == DialogResult.No) return false; // false halts the whole operation.
			return true;
		}

		const String SYNC_MESSAGE = @"
Data has been transferred and the synchronisation process started. 
Check the destination database for table {0}.
If it is present then either the synchronisation is still running or it failed
";

		const String CONFIRM_SYNC = @"
Table {0} exists in the target database. Existing
data will be overrwritten. Do you still want to proceed?
";

		const String CONFIRM_OVERWRITE = @"
{0} exists in the target database and would
be overrwritten. Do you still want to proceed?
";


	}

	public class CopyThreadClass
	{
		ContainerControl sender = null;
		Delegate senderDelegate = null;
		String sourceConnectionString;
		String targetConnectionString;
		String sourceTable;
		String targetTable;
		int rowCount = 0;
		bool isCancelled = false;
		public CopyThreadClass(ContainerControl sender, Delegate senderDelegate,
							   String connSource, String connTarget,
							   String sourceTable, String targetTable)
		{
			this.sender = sender;                 // ie the form with the controls to keep active
			this.senderDelegate = senderDelegate; // the function to safely invoke to update the progress
			this.sourceConnectionString = connSource;
			this.targetConnectionString = connTarget;
			this.sourceTable = sourceTable;
			this.targetTable = targetTable;
		}

		public void RunProcess()
		{
			this.LocalRunProcess();  // my sample code does it this way
		}

		// invoked when cancel is pressed
		public void CancelCopy(object sender, EventArgs e)
		{
			this.isCancelled = true;
		}

		private void LocalRunProcess()
		{
			// use worker thread to copy the content
			//m_RowCount = Convert.ToInt32(TableScripter.CountTableRows(this.m_connSource, m_SourceTable));
			this.rowCount = (int)SqlServerScripter.CountTableRows(this.sourceConnectionString, this.sourceTable);

			SqlConnection cnn = new SqlConnection(this.sourceConnectionString);
			// Getting source data. Have to cope with computed columns
			SqlTableSchema schema = Helper.MakeSchemaFor(this.sourceTable, this.sourceConnectionString);
			String fields = this.MakeCopyFieldList(schema);
			SqlCommand cmd = new SqlCommand(String.Format("SELECT {1} FROM {0}", this.sourceTable, fields), cnn);
			cnn.Open();
			SqlDataReader rdr = cmd.ExecuteReader();
			// Initializing an SqlBulkCopy object
			SqlBulkCopy sbc = new SqlBulkCopy(this.targetConnectionString, SqlBulkCopyOptions.KeepIdentity);
			this.CheckAddColumnMappings(sbc, schema);
			try
			{
				// Copying data to destination
				sbc.DestinationTableName = this.targetTable;
				sbc.BatchSize = 1000;    // 1000 rows at a time
				sbc.NotifyAfter = 1000;  // generate an event
				sbc.SqlRowsCopied += new SqlRowsCopiedEventHandler(this.CopyProgress);
				sbc.WriteToServer(rdr);
				
				// unless the rowcount is an exact multiple of the notify interval send once more to complete progess
				if ((this.rowCount % sbc.NotifyAfter) != 0)
					this.sender.BeginInvoke(this.senderDelegate, new object[] { this.rowCount, this.rowCount });
			}
			catch (OperationAbortedException)
			{
			}
			finally
			{
				// Closing connection and the others
				sbc.Close();
				rdr.Close();
				cnn.Close();
			}
		}

		// return all fields (including auto-inc keys)
		// *except* computed columns
		String MakeCopyFieldList(SqlTableSchema schema)
		{
			String list = "";
			for (int idx = 0; idx < schema.Count; ++idx)
			{
				if (!schema[idx].IsCalc)
				{
					if (list.Length > 0) list += ", ";
					list += "["+schema[idx].Name+"]";  // escape the name to be safe
				}
			}
			return list;
		}

		// only if there is a computed column
		// map all fields to themselves, but exclude computed columns.
		private void CheckAddColumnMappings(SqlBulkCopy sbc, SqlTableSchema schema)
		{
			// see whether this is all necessary (probably not).
			bool isComputedColumn = false;
			for (int idx = 0; idx < schema.Count; ++idx)
			{
				if (schema[idx].IsCalc) isComputedColumn = true;
			}
			if (!isComputedColumn) return; // nothing to do.
			// If necessary add the mappings leaving out the computed.
			for (int idx = 0; idx < schema.Count; ++idx)
			{
				if (!schema[idx].IsCalc)
				{
					sbc.ColumnMappings.Add(schema[idx].Name, schema[idx].Name);
				}
			}
		}

		private void CopyProgress(object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e)
		{
			if (this.isCancelled)
			{
				e.Abort = true;
			}
			else
			{
				int rowsCopied = Convert.ToInt32(e.RowsCopied);
				this.sender.BeginInvoke(this.senderDelegate, new object[] { this.rowCount, rowsCopied });
				e.Abort = false;
			}
		}
	}
	
	public class ContentRefreshThreadClass : WinFormThreading.WorkerBase
	{
		public ContentRefreshThreadClass(ContainerControl sender, String connStr, String script) : base (sender, new WinFormThreading.ProgressStateDelegate(Callback), new WinFormThreading.ThreadProcessState())
		{
			ProcessState.UserData = new String[] {connStr, script};
		}
		public static void Callback(WinFormThreading.ThreadProcessState processState)
		{
		}
		
		protected override void  DoWorkStep(WinFormThreading.ThreadProcessState processState)
		{
 			String [] data = (String[])processState.UserData;
 			String connStr = data[0];
 			String script = data[1];
 			String message = Helper.GenericExecute(connStr, script);
		}
	}

	
}
